package jdbc.util.CompositeQuery;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;


public class CompositeQueryDAO  implements CompositeQuery_interface{
	private static DataSource ds = null;
	static {
		try {
			Context ctx = new InitialContext();
			ds = (DataSource) ctx.lookup("java:comp/env/jdbc/CARPOOL");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}	
	
		private static final String GET_ALL_STMT = 
				"select cs.STOP_ID,cs.NAME ,cs.STOP_LONGITUDE,cs.STOP_LATITUDE ,csd.DRIVER_PATH_ID,csd.ARRIVAL ,cdp.DISTANCE,cdp.FEE,cdp.MAX_RIDER,cdp.MEMBER_ID from"
				+" carstop cs,carstop_detail csd , car_driver_path cdp  where csd.DRIVER_PATH_ID=cdp.DRIVER_PATH_ID and cs.stop_id=csd.stop_id ";


	


		public List<CompositeQueryVO> getAll() {
			List<CompositeQueryVO> list = new ArrayList<CompositeQueryVO>();
			CompositeQueryVO compositequeryVO=null;
			Connection con = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			try {

				con = ds.getConnection();
				pstmt = con.prepareStatement(GET_ALL_STMT);
				rs = pstmt.executeQuery();
				
				while (rs.next()) {
					compositequeryVO = new CompositeQueryVO();
					compositequeryVO.setStop_id(rs.getString("stop_id"));
					compositequeryVO.setName(rs.getString("name"));
					compositequeryVO.setStop_longitude(rs.getDouble("STOP_LONGITUDE"));
					compositequeryVO.setStop_latitude(rs.getDouble("STOP_LATITUDE"));
					compositequeryVO.setDriver_path_id(rs.getString("DRIVER_PATH_ID"));
					compositequeryVO.setArrival(rs.getTimestamp("arrival"));
					compositequeryVO.setDistance(rs.getInt("DISTANCE"));
					compositequeryVO.setFee(rs.getInt("fee"));
					compositequeryVO.setMaxrider(rs.getInt("MAX_RIDER"));
					compositequeryVO.setMember_id(rs.getString("MEMBER_ID"));					
					list.add(compositequeryVO);
				}
				
			} catch (SQLException se) {
				throw new RuntimeException("A database error occured. "
						+ se.getMessage());
				// Clean up JDBC resources
			} finally {
				if (rs != null) {
					try {
						rs.close();
					} catch (SQLException se) {
						se.printStackTrace(System.err);
					}
				}
				if (pstmt != null) {
					try {
						pstmt.close();
					} catch (SQLException se) {
						se.printStackTrace(System.err);
					}
				}
				if (con != null) {
					try {
						con.close();
					} catch (Exception e) {
						e.printStackTrace(System.err);
					}
				}
			}
			return list;
		}





		public List<CompositeQueryVO> getAll(StringBuffer whereCondition) {
			List<CompositeQueryVO> list = new ArrayList<CompositeQueryVO>();
			CompositeQueryVO compositequeryVO=null;
			Connection con = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			try {

				con = ds.getConnection();
				pstmt = con.prepareStatement(GET_ALL_STMT+whereCondition);
				rs = pstmt.executeQuery();
				
				while (rs.next()) {
					compositequeryVO = new CompositeQueryVO();
					compositequeryVO.setStop_id(rs.getString("stop_id"));
					compositequeryVO.setName(rs.getString("name"));
					compositequeryVO.setStop_longitude(rs.getDouble("STOP_LONGITUDE"));
					compositequeryVO.setStop_latitude(rs.getDouble("STOP_LATITUDE"));
					compositequeryVO.setDriver_path_id(rs.getString("DRIVER_PATH_ID"));
					compositequeryVO.setArrival(rs.getTimestamp("arrival"));
					compositequeryVO.setDistance(rs.getInt("DISTANCE"));
					compositequeryVO.setFee(rs.getInt("fee"));
					compositequeryVO.setMaxrider(rs.getInt("MAX_RIDER"));
					compositequeryVO.setMember_id(rs.getString("MEMBER_ID"));					
					list.add(compositequeryVO);
				}
				
			} catch (SQLException se) {
				throw new RuntimeException("A database error occured. "
						+ se.getMessage());
				// Clean up JDBC resources
			} finally {
				if (rs != null) {
					try {
						rs.close();
					} catch (SQLException se) {
						se.printStackTrace(System.err);
					}
				}
				if (pstmt != null) {
					try {
						pstmt.close();
					} catch (SQLException se) {
						se.printStackTrace(System.err);
					}
				}
				if (con != null) {
					try {
						con.close();
					} catch (Exception e) {
						e.printStackTrace(System.err);
					}
				}
			}
			return list;
		}

	
	
		
	
	
}
